<?php
class ACTION{
    public static $PDO = null;
    
    public static function sqlInit() {
        try{
           self::$PDO = new PDO('mysql:host='.SQL_HOST.';port='.SQL_PORT.';dbname='.SQL_DB.';charset='.SQL_CHARSET,SQL_USER,SQL_PWD,array(PDO::MYSQL_ATTR_INIT_COMMAND => 'set names '.SQL_CHARSET,PDO::ATTR_PERSISTENT => SQL_PERSISTENT)); 
        }catch (Exception $error){
            exit('Failed to connect MySQL Server! ' . $error->getMessage());
        }
    }
    
    public static function login($type,$acc = '',$pwd = '') {
        $statement = self::$PDO->prepare('SELECT * FROM `user` WHERE type = :type AND (`name` = :acc OR `idcard` = :acc OR `number` = :acc)  LIMIT 1');
        $statement->bindValue(':type',$type);
        $statement->bindValue(':acc',$acc);
        $statement->execute();
        $result = $statement->fetch(PDO::FETCH_ASSOC);
        if(!$result){
            if($type == 1){return array('code' => 0,'msg' => '无此[管理员]账号...');}
            if($type == 2){return array('code' => 0,'msg' => '无此[教师]账号...');}
            if($type == 3){return array('code' => 0,'msg' => '无此[学生]账号...');}
        }
        if(strtoupper(hash('md5',$pwd)) !== $result['pwd']){return array('code' => 0,'msg' => '密码输入错误'); exit();}
        $_SESSION = array();
        $_SESSION['user_id'] = $result['id'];
        $_SESSION['user_type'] = $result['type'];
        $_SESSION['user_idcard'] = $result['idcard'];
        $_SESSION['user_number'] = $result['number'];
        $_SESSION['user_classes'] = $result['classes'];
        return array('code' => 1,'msg' => '登录成功');
    }
    
    public static function sign_out() {
        $_SESSION = array();
        session_destroy();
        return array('code' => 1,'msg' => '退出成功！');
    }
    public static function insertCourse($name = '') {
        $statement = self::$PDO->prepare('INSERT INTO `course` SET `name` = :name,`add_time` = :add_time');
        $statement->bindValue(':name',$name);
        $statement->bindValue(':add_time',time());
        $result = $statement->execute();
        if($result){
            return array('code' => 1,'msg' => '增加课程成功！');
        }else{
            return array('code' => 0,'msg' => '增加课程失败！');
        }
    }
    public static function getCourse(){
        $statement = self::$PDO->prepare('SELECT `id`,`name`,FROM_UNIXTIME(`add_time`,"%Y-%m-%d") AS `add_time` FROM `course`');
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        if($result){
            return array('code' => 1,'msg' => $result);
        }else{
            return array('code' => 0,'msg' => '获取课程失败！');
        }
    }
    public static function insertClass($name = '') {
        $statement = self::$PDO->prepare('INSERT INTO `class` SET `name` = :name,`add_time` = :add_time');
        $statement->bindValue(':name',$name);
        $statement->bindValue(':add_time',time());
        $result = $statement->execute();
        if($result){
            return array('code' => 1,'msg' => '增加班级成功！');
        }else{
            return array('code' => 0,'msg' => '增加班级失败！');
        }
    }
    public static function insertExam_select($course_id = '',$course_name = '',$T = '',$Y = '',$M = array()){
        $statement = self::$PDO->prepare('INSERT INTO `select` SET `course_id` = :course_id,`course_name` = :course_name,`T` = :T,`Y` = :Y,`A` = :A,`B` = :B,`C` = :C,`D` = :D,`itime` = :itime');
        $statement->bindValue(':course_id',$course_id);
        $statement->bindValue(':course_name',htmlspecialchars($course_name));
        $statement->bindValue(':T',htmlspecialchars($T));
        $statement->bindValue(':Y',htmlspecialchars($Y));
        $statement->bindValue(':A',htmlspecialchars($M[0]));
        $statement->bindValue(':B',htmlspecialchars($M[1]));
        $statement->bindValue(':C',htmlspecialchars($M[2]));
        $statement->bindValue(':D',htmlspecialchars($M[3]));
        $statement->bindValue(':itime',time());
        $result = $statement->execute();
        if($result){
            return array('code' => 1,'msg' => '增加选择题成功！');
        }else{
            return array('code' => 0,'msg' => '增加选择题失败！');
        }
    }
    public static function insertExam_yesorno($course_id = '',$course_name = '',$question = '',$answer = '') {
        $statement = self::$PDO->prepare('INSERT INTO `yesorno` SET `course_id` = :course_id,`course_name` = :course_name,`question` = :question,`answer` = :answer,`itime` = :itime');
        $statement->bindValue(':course_id',$course_id);
        $statement->bindValue(':course_name',htmlspecialchars($course_name));
        $statement->bindValue(':question',htmlspecialchars($question));
        $statement->bindValue(':answer',htmlspecialchars($answer));
        $statement->bindValue(':itime',time());
        $result = $statement->execute();
        if($result){
            return array('code' => 1,'msg' => '增加对错题成功！');
        }else{
            return array('code' => 0,'msg' => '增加对错题失败！');
        }
    }
    public static function insertExam_read($course_id = '',$course_name = '',$question = '') {
        $statement = self::$PDO->prepare('INSERT INTO `read` SET `course_id` = :course_id,`course_name` = :course_name,`question` = :question,`itime` = :itime');
        $statement->bindValue(':course_id',$course_id);
        $statement->bindValue(':course_name',$course_name);
        $statement->bindValue(':question',$question);
        $statement->bindValue(':itime',time());
        $result = $statement->execute();
        if($result){
            return array('code' => 1,'msg' => '增加阅读题成功！');
        }else{
            return array('code' => 0,'msg' => '增加阅读题失败！');
        }
    }
    public static function getClass(){
        $statement = self::$PDO->prepare('SELECT `id`,`name`,FROM_UNIXTIME(`add_time`,"%Y-%m-%d") AS `add_time` FROM `class`');
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        if($result){
            return array('code' => 1,'msg' => $result);
        }else{
            return array('code' => 0,'msg' => '获取班级失败！');
        }
    }
    public static function insertTeacher($name = '',$sex = 0,$classes) {
        $statement = self::$PDO->prepare('INSERT INTO `user` SET `type` = 2,`name` = :name,`pwd` = :pwd,`sex` = :sex,`classes` = :classes,`itime` = :itime');
        $statement->bindValue(':name',$name);
        $statement->bindValue(':pwd','21218CCA77804D2BA1922C33E0151105');
        $statement->bindValue(':sex',$sex);
        $statement->bindValue(':classes',$classes);
        $statement->bindValue(':itime',time());
        $result = $statement->execute();
        if($result){
            return array('code' => 1,'msg' => '增加教师成功！');
        }else{
            return array('code' => 0,'msg' => '增加教师失败！');
        }
    }
    public static function getAllTeacher(){
        $statement = self::$PDO->prepare('SELECT `id`,`name`,`sex`,`classes`,FROM_UNIXTIME(`itime`,"%Y-%m-%d") AS `itime` FROM `user` WHERE `type` = 2');
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        if($result){
            return array('code' => 1,'msg' => $result);
        }else{
            return array('code' => 0,'msg' => '获取教师失败！');
        }
    }
    public static function getExamOfselect(){
        $statement = self::$PDO->prepare('SELECT `course_id`,`course_name`,`id`,`T`,`A`,`B`,`C`,`D`,FROM_UNIXTIME(`itime`,"%Y-%m-%d") AS `itime` FROM `select`');
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        if($result){
            return array('code' => 1,'msg' => $result);
        }else{
            return array('code' => 0,'msg' => '获取试题失败！');
        }
    }
    public static function getExamOfyesorno(){
        $statement = self::$PDO->prepare('SELECT `course_id`,`course_name`,`id`,`question`,`answer`,FROM_UNIXTIME(`itime`,"%Y-%m-%d") AS `itime` FROM `yesorno`');
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        if($result){
            return array('code' => 1,'msg' => $result);
        }else{
            return array('code' => 0,'msg' => '获取试题失败！');
        }
    }
    public static function getExamOfread(){
        $statement = self::$PDO->prepare('SELECT `course_id`,`course_name`,`id`,`question`,FROM_UNIXTIME(`itime`,"%Y-%m-%d") AS `itime` FROM `read`');
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        if($result){
            return array('code' => 1,'msg' => $result);
        }else{
            return array('code' => 0,'msg' => '获取试题失败！');
        }
    }
    public static function insertStudent($idcard = '',$name = '',$sex = 0,$classes = 0) {
        $statement = self::$PDO->prepare('INSERT INTO `user` SET `type` = 3,`idcard` = :idcard,`name` = :name,`pwd` = :pwd,`sex` = :sex,`classes` = :classes,`itime` = :itime');
        $statement->bindValue(':idcard',$idcard);
        $statement->bindValue(':name',$name);
        $statement->bindValue(':pwd','E10ADC3949BA59ABBE56E057F20F883E');
        $statement->bindValue(':sex',$sex);
        $statement->bindValue(':classes',$classes);
        $statement->bindValue(':itime',time());
        $result = $statement->execute();
        if($result){
            $id = self::$PDO->lastInsertId();
            $number = substr($id.rand(100000,999999),0,7);
            $statement = self::$PDO->prepare('UPDATE `user` SET `number` = :number WHERE `id` = :id LIMIT 1');
            $statement->bindValue(':number',$number);
            $statement->bindValue(':id',$id);
            $result = $statement->execute();
            if($result){
                return array('code' => 1,'msg' => '更新学生成功！');
            }else{
                return array('code' => 0,'msg' => '更新学生失败！');
            }
        }else{
            return array('code' => 0,'msg' => '增加学生失败！');
        }
    }
    public static function getStudent(){
        $statement = self::$PDO->prepare('SELECT `id`,`idcard`,`number`,`name`,`sex`,`classes`,FROM_UNIXTIME(`itime`,"%Y-%m-%d") AS `itime` FROM `user` WHERE `type` = 3');
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        if($result){
            return array('code' => 1,'msg' => $result);
        }else{
            return array('code' => 0,'msg' => '获取学生失败！');
        }
    }
    public static function getStudentsOnlyClasses(){
        $statement = self::$PDO->prepare('SELECT `id`,`idcard`,`number`,`name`,`sex`,`classes`,FROM_UNIXTIME(`itime`,"%Y-%m-%d") AS `itime` FROM `user` WHERE `type` = 3 AND `classes`=:classes');
        $statement->bindValue(':classes',$_SESSION['user_classes']);
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        if($result){
            return array('code' => 1,'msg' => $result);
        }else{
            return array('code' => 0,'msg' => '获取特定班级所有学生失败！');
        }
    }
    public static function getStudentsOnlyClasses_1(){
        $statement = self::$PDO->prepare('SELECT A.`id`,A.`idcard`,A.`number`,A.`name`,A.`sex`,A.`classes`,FROM_UNIXTIME(A.`itime`,"%Y-%m-%d") AS `itime`,B.`name` AS `class_name` FROM `user` AS A LEFT JOIN `class` AS B ON A.`classes` = B.`id` WHERE A.`type` = 3 AND A.`classes` = :classes');
        $statement->bindValue(':classes',$_SESSION['user_classes']);
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        if($result){
            return array('code' => 1,'msg' => $result);
        }else{
            return array('code' => 0,'msg' => '获取特定班级所有学生失败！');
        }
    }
    
    public static function get_teacher_exams(){
        $statement = self::$PDO->prepare('SELECT A.`id`,A.`student_id`,A.`classes_id`,A.`course_id`,A.`req_data`,A.`used_time`,FROM_UNIXTIME(A.`itime`,"%Y-%m-%d") AS `itime`,B.`name` AS `student_name`,C.`name` AS `class_name`,D.`name` AS `course_name` FROM `exam` AS A LEFT JOIN `user` AS B ON A.`student_id` = B.`id` LEFT JOIN `class` AS C ON A.`classes_id` = C.`id` LEFT JOIN `course` AS D ON A.`course_id` = D.`id` WHERE A.`classes_id` = 1 AND A.`status` = 0');
        $statement->bindValue(':classes_id',$_SESSION['user_classes']);
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        if($result){
            return array('code' => 1,'msg' => $result);
        }else{
            return array('code' => 0,'msg' => '获取特定班级所有试卷失败！');
        }
    }
    public static function getExamsOfStudent(){
        $statement = self::$PDO->prepare('SELECT A.`id`,A.`used_time`,FROM_UNIXTIME(A.`itime`,"%Y-%m-%d") AS `itime`,A.`score`,B.`name` AS `course_name` FROM `exam` AS A LEFT JOIN `course` AS B ON A.`course_id` = B.`id` WHERE A.`student_id` = :student_id AND A.`status` = 1');
        $statement->bindValue(':student_id',$_SESSION['user_id']);
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        if($result){
            return array('code' => 1,'msg' => $result);
        }else{
            return array('code' => 0,'msg' => '获取特定学生所有试卷失败！');
        }
    }
    
    public static function get_teacher_exams_done(){
        $statement = self::$PDO->prepare('SELECT A.`id`,A.`student_id`,A.`classes_id`,A.`course_id`,A.`req_data`,A.`used_time`,FROM_UNIXTIME(A.`itime`,"%Y-%m-%d") AS `itime`,B.`name` AS `student_name`,C.`name` AS `class_name`,D.`name` AS `course_name` FROM `exam` AS A LEFT JOIN `user` AS B ON A.`student_id` = B.`id` LEFT JOIN `class` AS C ON A.`classes_id` = C.`id` LEFT JOIN `course` AS D ON A.`course_id` = D.`id` WHERE A.`classes_id` = 1 AND A.`status` = 1');
        $statement->bindValue(':classes_id',$_SESSION['user_classes']);
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        if($result){
            return array('code' => 1,'msg' => $result);
        }else{
            return array('code' => 0,'msg' => '获取特定班级所有已阅试卷失败！');
        }
    }
    public static function getStudentInfo(){
        $statement = self::$PDO->prepare('SELECT * FROM `user` WHERE `type` = 3 AND id = :id');
        $statement->bindValue(':id',$_SESSION['user_id']);
        $statement->execute();
        $result = $statement->fetch(PDO::FETCH_ASSOC);
        if($result){
            return array('code' => 1,'msg' => $result);
        }else{
            return array('code' => 0,'msg' => '获取学生信息失败！');
        }
    }
    public static function getTeacherInfo(){
        $statement = self::$PDO->prepare('SELECT * FROM `user` WHERE `type` = 2 AND id = :id');
        $statement->bindValue(':id',$_SESSION['user_id']);
        $statement->execute();
        $result = $statement->fetch(PDO::FETCH_ASSOC);
        if($result){
            return array('code' => 1,'msg' => $result);
        }else{
            return array('code' => 0,'msg' => '获取教师信息失败！');
        }
    }
    public static function submitExam($req_data,$used_time,$course_id){
        $st = self::$PDO->prepare('SELECT `Y` FROM `select` WHERE `id` = :id');
        foreach(array_slice($req_data,0,-15) as $k => $v){
            $st->bindValue(':id',$v['id']);
            $st->execute();
            $res = $st->fetch(PDO::FETCH_ASSOC);
            if($res){
                if($res['Y'] == $v['answer']){
                    $req_data[$k]['result'] = '1';
                    $req_data[$k]['score'] = '2';
                }
            }
        }
        $st = self::$PDO->prepare('SELECT `answer` FROM `yesorno` WHERE `id` = :id');
        foreach(array_slice($req_data,20,-5) as $k => $v){
            $st->bindValue(':id',$v['id']);
            $st->execute();
            $res = $st->fetch(PDO::FETCH_ASSOC);
            if($res){
                if($res['answer'] == $v['answer']){
                    $req_data[$k]['result'] = '1';
                    $req_data[$k]['score'] = '2';
                }
            }
        }
        $statement = self::$PDO->prepare('INSERT INTO `exam` (`student_id`,`classes_id`,`course_id`,`req_data`,`used_time`,`itime`) VALUES (:student_id,:classes_id,:course_id,:req_data,:used_time,:itime)');
        $statement->bindValue(':student_id',$_SESSION['user_id']);
        $statement->bindValue(':classes_id',$_SESSION['user_classes']);
        $statement->bindValue(':course_id',$course_id);
        $statement->bindValue(':req_data',json_encode($req_data));
        $statement->bindValue(':used_time',$used_time);
        $statement->bindValue(':itime',time());
        $result = $statement->execute();
        if($result){
            return array('code' => 1,'msg' => '提交试卷成功！');
        }else{
            return array('code' => 0,'msg' => '提交试卷失败！');
        }
    }
    
    public static function getExamOne($exam_id){
        $score = 0;
        $st = self::$PDO->prepare('SELECT * FROM `exam` WHERE `id` = :id LIMIT 1');
        $st->bindValue(':id',$exam_id);
        $st->execute();
        $res = $st->fetch(PDO::FETCH_ASSOC);
        foreach(array_slice(json_decode($res['req_data'],true),0,29) as $k => $v){
            $score += (int) $v['score'];
        }
        $res['req_data'] = array_slice(json_decode($res['req_data'],true),-5);
        $st_2 = self::$PDO->prepare('SELECT `question` FROM `read` WHERE `id` = :id LIMIT 1');
        
        foreach($res['req_data'] as $k => $v){
            $st_2->bindValue(':id',$v['id']);
            $st_2->execute();
            $res_2 = $st_2->fetch(PDO::FETCH_ASSOC);
            $res['req_data'][$k]['question'] = $res_2['question'];
        }
        if($res){
            return array('code' => 1,'msg' => $res,'score' => $score);
        }else{
            return array('code' => 0,'msg' => '获取试卷失败！');
        }
    }
    public static function getSelectSubject(){
        $st = self::$PDO->prepare('SELECT * FROM `select` ORDER BY RAND() LIMIT 20');
        $st->execute();
        $res = $st->fetchAll(PDO::FETCH_ASSOC);
        if($res){
            return array('code' => 1,'msg' => $res);
        }else{
            return array('code' => 0,'msg' => '获取选择题失败！');
        }
    }
    public static function getYesornoSubject(){
        $st = self::$PDO->prepare('SELECT * FROM `yesorno` ORDER BY RAND() LIMIT 10');
        $st->execute();
        $res = $st->fetchAll(PDO::FETCH_ASSOC);
        if($res){
            return array('code' => 1,'msg' => $res);
        }else{
            return array('code' => 0,'msg' => '获取对错题失败！');
        }
    }
    public static function getReadSubject(){
        $st = self::$PDO->prepare('SELECT * FROM `read` ORDER BY RAND() LIMIT 5');
        $st->execute();
        $res = $st->fetchAll(PDO::FETCH_ASSOC);
        if($res){
            return array('code' => 1,'msg' => $res);
        }else{
            return array('code' => 0,'msg' => '获取解答题失败！');
        }
    }
    
    public static function deleteCourse($id){
        $statement = self::$PDO->prepare('DELETE FROM `course` WHERE `id` = :id LIMIT 1');
        $statement->bindValue(':id',$id);
        $result = $statement->execute();
        if($result){
            return array('code' => 1,'msg' => '删除课程成功！');
        }else{
            return array('code' => 0,'msg' => '删除课程失败！');
        }
    }
    public static function deleteClass($id){
        $statement = self::$PDO->prepare('DELETE FROM `class` WHERE `id` = :id LIMIT 1');
        $statement->bindValue(':id',$id);
        $result = $statement->execute();
        if($result){
            return array('code' => 1,'msg' => '删除班级成功！');
        }else{
            return array('code' => 0,'msg' => '删除班级失败！');
        }
    }
    public static function teacher_view_submit($id,$score){
        $statement = self::$PDO->prepare('UPDATE `exam` SET `score` = :score,status = 1 WHERE `id` = :id LIMIT 1');
        $statement->bindValue(':id',$id);
        $statement->bindValue(':score',$score);
        $result = $statement->execute();
        if($result){
            return array('code' => 1,'msg' => '教师打分成功！');
        }else{
            return array('code' => 0,'msg' => '教师打分失败！');
        }
    }
    
    public static function deleteTeacher($id){
        $statement = self::$PDO->prepare('DELETE FROM `user` WHERE type = 2 AND `id` = :id LIMIT 1');
        $statement->bindValue(':id',$id);
        $result = $statement->execute();
        if($result){
            return array('code' => 1,'msg' => '删除教师成功！');
        }else{
            return array('code' => 0,'msg' => '删除教师失败！');
        }
    }
    public static function deleteStudent($id){
        $statement = self::$PDO->prepare('DELETE FROM `user` WHERE type = 3 AND `id` = :id LIMIT 1');
        $statement->bindValue(':id',$id);
        $result = $statement->execute();
        if($result){
            return array('code' => 1,'msg' => '删除学生成功！');
        }else{
            return array('code' => 0,'msg' => '删除学生失败！');
        }
    }
    public static function deleteExam($id){
        $statement = self::$PDO->prepare('DELETE FROM `choice` WHERE `id` = :id LIMIT 1');
        $statement->bindValue(':id',$id);
        $result = $statement->execute();
        if($result){
            return array('code' => 1,'msg' => '删除试题成功！');
        }else{
            return array('code' => 0,'msg' => '删除试题失败！');
        }
    }
}